package cn.goktech.dao;

import ch.qos.logback.core.db.dialect.DBUtil;
import cn.goktech.entity.Column;
import cn.goktech.entity.Table;
import cn.goktech.properties.DBConnectionProperties;
import cn.goktech.util.DBConnUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName DBInfoDAO
 * @Discription 获取数据库相关信息
 * @Author Carter
 * @Date 2021/4/18 11:07
 * @Version 1.0
 **/
@Repository
public class DBInfoDAO {

    @Autowired
    private DBConnectionProperties dbConnectionProperties;

    /**
     * 查询所连接的主机所有数据库名称
     * @return
     */
    public List<Map<String,String>> getAllDataBase(){
        List<Map<String,String>> allDataBaseList = new ArrayList<>();
        Connection connection = DBConnUtil.getConnection(dbConnectionProperties,"mysql");
        Statement statement = null;
        try {
            statement = connection.createStatement();
            ResultSet allDataBases = statement.executeQuery("SHOW DATABASES");

            while(allDataBases.next()){
                Map<String,String> resultMap = new HashMap<>();
                resultMap.put("value",allDataBases.getString(1));
                allDataBaseList.add(resultMap);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return allDataBaseList;
    }

    /**
     * 根据制定数据库名称查询指定数据库中所有的表信息
     * 包含该表中所有的字段信息
     * @param dbName
     * @return
     */
    public List<Table> getTableByDBName(String dbName){
        //获取数据库连接
        Connection connection = DBConnUtil.getConnection(dbConnectionProperties,dbName);
        List<Table> resultList = new ArrayList<>();
        //获取数据库元数据
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            //查询指定数据库中的所有表
            ResultSet dbInfo = metaData.getTables(dbName, null, null, new String[]{"TABLE"});
            while(dbInfo.next()){
                Table tableItem = new Table();
                List<Column> itemColumn = new ArrayList<>();

                //获取表相关信息
                tableItem.setTableRemark(dbInfo.getString("REMARKS"));//表的备注
                tableItem.setDbTable(dbInfo.getString("TABLE_NAME"));//表名
                ResultSet rs4Primary =  metaData.getPrimaryKeys(null,null,dbInfo.getString("TABLE_NAME"));
                while(rs4Primary.next()){
                    tableItem.setPrimaryKey(rs4Primary.getString("COLUMN_NAME"));
                }

                //获取制定表的列信息
                String sql = "SELECT * FROM "+dbInfo.getString("TABLE_NAME");
                PreparedStatement ps = connection.prepareStatement(sql);

                ResultSetMetaData columnInfoRs = ps.executeQuery().getMetaData();//获取表查询的结果集

                for(int i = 1;i<=columnInfoRs.getColumnCount();i++){
                    Column column = new Column();
                    column.setDbColumnName(columnInfoRs.getColumnName(i));
                    column.setDbColumnType(columnInfoRs.getColumnTypeName(i));
                    itemColumn.add(column);
                }
                tableItem.setTableColumn(itemColumn);
                resultList.add(tableItem);
                ps.close();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return resultList;
    }
}
